In [13]:
import pandas as pd
from pandas import read_sql_table
import numpy as np
from sklearn.ensemble import RandomForestRegressor, GradientBoostingRegressor
from sklearn.metrics import mean_absolute_error, mean_squared_error, r2_score
from matplotlib import pyplot
from sklearn.model_selection import train_test_split, cross_val_score, GridSearchCV
from sklearn.decomposition import PCA
from sklearn.preprocessing import StandardScaler
import numpy as np
import pandas as pd
from pandas_profiling import ProfileReport
In [6]:
SQLALCHEMY_DATABASE_URI = 'postgresql://{db_user}:{db_password}@{db_host}:5432/{db_name}'.format(
db_host = '34.69.215.94',
db_name = 'snpiao_data',
db_password = 'ucamnafinki',
db_user = 'snpiao_data',
)
In [8]:
energy = read_sql_table('tsdb_fronius_energy',SQLALCHEMY_DATABASE_URI)
cams = read_sql_table('tsdb_cams', SQLALCHEMY_DATABASE_URI)
merra = read_sql_table('tsdb_merra', SQLALCHEMY_DATABASE_URI)
In [9]:
energy = energy.set_index('datetime').sort_index()
cams = cams.set_index('datetime').sort_index()
merra = merra.set_index('datetime').sort_index()
energy = energy.resample('1H').agg('mean')
merged = cams.merge(energy, on='datetime')
merged = merged.merge(merra, on='datetime')
merged.fillna(method='ffill', inplace=True)
merged['hour'] = merged.index.hour
merged['dayofweek'] = merged.index.dayofweek
merged['consumption'] = merged['FromGenToConsumer'] + merged['FromGridToConsumer']
pyplot.plot(merged['consumption'])
Out[9]:
In [11]:
q = merged['consumption'].quantile(0.99)
merged = merged[merged['consumption'] < q]
pyplot.plot(merged['consumption'])
pyplot.show()
In [12]:
merged
Out[12]:
In [14]:
profile = ProfileReport(merged, title="Pandas Profiling Report")
In [15]:
profile
Out[15]:
In [21]:
X = pd.DataFrame(data=merged, columns=['gh', 'csky_ghi', 'tdry', 'wspd', 'wdir' 'pres_hpa', 'rhum', 'snowd_m', 'rainfall'])
y = pd.DataFrame(data=merged, columns=['consumption'])
to_csv = pd.DataFrame(data = merged, columns=['gh', 'csky_ghi', 'tdry', 'wspd', 'pres_hpa', 'rhum', 'snowd_m', 'rainfall', 'consumption'])
In [17]:
y.plot()
Out[17]:
In [22]:
to_csv
Out[22]:
In [ ]: